package cs304.p3.library.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.google.common.collect.Lists;

import cs304.p3.library.model.DBTableModel.TableType;

public class DevDao extends Dao {
	
	public List<String[]> display(TableType table) throws SQLException {
		List<String[]> rows = Lists.newArrayList();
		String sql = "SELECT * FROM " + table.toString();
		PreparedStatement selectAll = connection.prepareStatement(sql);
		ResultSet rs = selectAll.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		while(rs.next()) {
			String[] row = new String[rsmd.getColumnCount()];
			for(int i=0; i<row.length; i++) {
				row[i] = rs.getString(i+1);
			}
			rows.add(row);
		}
		return rows;
	}

	public void insert(TableType table, String[] values) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO " + table.toString());
		sql.append(" VALUES (");
		for(int i=0; i<values.length; i++) {
			sql.append("'");
			sql.append(values[i]);
			sql.append("'");
			if(i!=values.length-1)
				sql.append(",");
			else
				sql.append(")");
		}
		System.out.println("Insert SQL: " + sql.toString());
		PreparedStatement insert = connection.prepareStatement(sql.toString());
		insert.executeUpdate();
	}

	public void delete(TableType table, Map<String, String> map) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM " + table.toString());
		sql.append(" WHERE ");
		int i = 0;
		for(String key : map.keySet()) {
			String value = map.get(key);
			sql.append(key);
			sql.append("= '");
			sql.append(value);
			sql.append("'");
			
			if(i != map.size()-1)
				sql.append(" AND ");
			
			i++;
		}
		System.out.println("Delete SQL: " + sql.toString());
		PreparedStatement delete = connection.prepareStatement(sql.toString());
		delete.executeUpdate();
	}
	
	public List<String> selectTables() throws SQLException {
		String sql = "SELECT table_name FROM user_tables";
		PreparedStatement ps = connection.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<String> tables = Lists.newArrayList();
		while(rs.next()) {
			tables.add(rs.getString(1));
		}
		return tables;
	}
	
}
